package com.example.myapplication

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import java.text.SimpleDateFormat
import java.util.*
import android.util.Log
import androidx.annotation.WorkerThread
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.withContext

class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    
    // 注释掉删除数据库的代码，避免每次启动都重置数据
    // init {
    //     context.deleteDatabase(DATABASE_NAME)
    // }

    // 数据库连接管理
    private var readableDB: SQLiteDatabase? = null
    private var writableDB: SQLiteDatabase? = null
    
    // 获取只读数据库连接（复用连接）
    @Synchronized
    private fun getReadableDB(): SQLiteDatabase {
        if (readableDB == null || !readableDB!!.isOpen) {
            readableDB = super.getReadableDatabase()
        }
        return readableDB!!
    }
    
    // 获取可写数据库连接（复用连接）
    @Synchronized
    private fun getWritableDB(): SQLiteDatabase {
        if (writableDB == null || !writableDB!!.isOpen) {
            writableDB = super.getWritableDatabase()
        }
        return writableDB!!
    }
    
    // 关闭数据库连接
    @Synchronized
    override fun close() {
        readableDB?.close()
        writableDB?.close()
        readableDB = null
        writableDB = null
        super.close()
    }

    companion object {
        private const val DATABASE_NAME = "PatientDatabase"
        private const val DATABASE_VERSION = 7
        private const val TABLE_PATIENTS = "patients"
        private const val TABLE_USERS = "users"
        private const val TABLE_DEPARTMENTS = "departments"
        private const val TABLE_APP_STATUS = "app_status"
        private const val TABLE_NOTIFICATIONS = "notifications"
        
        // 患者表字段
        private const val KEY_ID = "id"
        private const val KEY_NAME = "name"
        private const val KEY_EXAMINATION_DATE = "examination_date"
        private const val KEY_REPORTER = "reporter"
        private const val KEY_EXAMINATION_NAME = "examination_name"
        private const val KEY_DEPARTMENT = "department"
        private const val KEY_REPORTED_BY = "reported_by"
        private const val KEY_REPORTED_BY_TYPE = "reported_by_type"
        private const val KEY_REPORT_TIME = "report_time"
        
        // 用户表字段
        private const val KEY_USER_ID = "id"
        private const val KEY_USERNAME = "username"
        private const val KEY_PASSWORD = "password"
        private const val KEY_REAL_NAME = "real_name"
        private const val KEY_USER_DEPARTMENT = "department"
        private const val KEY_USER_TYPE = "user_type"
        private const val KEY_IS_ACTIVE = "is_active"
        private const val KEY_IS_APPROVED = "is_approved"
        private const val KEY_APPROVED_BY = "approved_by"
        private const val KEY_APPROVED_TIME = "approved_time"
        private const val KEY_PHONE = "phone"
        // 删除：private const val KEY_EMAIL = "email"
        
        // 科室表字段
        private const val KEY_DEPT_ID = "id"
        private const val KEY_DEPT_NAME = "name"
        private const val KEY_DEPT_ADMIN_ID = "admin_id"
        private const val KEY_DEPT_IS_ACTIVE = "is_active"
        private const val KEY_DEPT_DESCRIPTION = "description"
        private const val KEY_DEPT_CONTACT_PHONE = "contactPhone"
        private const val KEY_DEPT_ADMIN_NAME = "adminName"
        private const val KEY_DEPT_CREATED_AT = "created_at"
        
        // 应用状态表字段
        private const val KEY_STATUS_ID = "id"
        private const val KEY_IS_ENABLED = "is_enabled"
        private const val KEY_ENABLED_BY = "enabled_by"
        private const val KEY_ENABLED_TIME = "enabled_time"
        private const val KEY_DISABLED_BY = "disabled_by"
        private const val KEY_DISABLED_TIME = "disabled_time"
        
        // 通知表字段
        private const val KEY_NOTIFICATION_ID = "id"
        private const val KEY_NOTIFICATION_TITLE = "title"
        private const val KEY_NOTIFICATION_MESSAGE = "message"
        private const val KEY_NOTIFICATION_TYPE = "type"
        private const val KEY_NOTIFICATION_TARGET_USER_ID = "target_user_id"
        private const val KEY_NOTIFICATION_TARGET_DEPARTMENT = "target_department"
        private const val KEY_NOTIFICATION_IS_READ = "is_read"
        private const val KEY_NOTIFICATION_CREATE_TIME = "create_time"
        private const val KEY_NOTIFICATION_CREATED_BY = "created_by"
    }

    override fun onCreate(db: SQLiteDatabase) {
        // 创建患者表
        val createPatientsTable = ("CREATE TABLE " + TABLE_PATIENTS + "("
                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_NAME + " TEXT,"
                + "gender TEXT,"
                + KEY_EXAMINATION_DATE + " TEXT,"
                + "admissionDate TEXT,"
                + "diagnosis TEXT,"
                + "notes TEXT,"
                + KEY_REPORTER + " TEXT,"
                + KEY_EXAMINATION_NAME + " TEXT,"
                + KEY_DEPARTMENT + " TEXT,"
                + KEY_REPORTED_BY + " INTEGER,"
                + KEY_REPORTED_BY_TYPE + " TEXT,"
                + KEY_REPORT_TIME + " TEXT"
                + ")")
        
        // 创建用户表
        val createUsersTable = ("CREATE TABLE " + TABLE_USERS + "("
                + KEY_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_USERNAME + " TEXT UNIQUE,"
                + KEY_PASSWORD + " TEXT,"
                + KEY_REAL_NAME + " TEXT,"
                + KEY_USER_DEPARTMENT + " TEXT,"
                + KEY_USER_TYPE + " TEXT,"
                + KEY_IS_ACTIVE + " INTEGER,"
                + KEY_IS_APPROVED + " INTEGER,"
                + KEY_APPROVED_BY + " INTEGER,"
                + KEY_APPROVED_TIME + " INTEGER,"
                + KEY_PHONE + " TEXT"
                + ")")
        
        // 创建科室表
        val createDepartmentsTable = ("CREATE TABLE " + TABLE_DEPARTMENTS + "(" 
                + KEY_DEPT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 
                + KEY_DEPT_NAME + " TEXT UNIQUE," 
                + KEY_DEPT_ADMIN_ID + " INTEGER," 
                + KEY_DEPT_IS_ACTIVE + " INTEGER," 
                + KEY_DEPT_DESCRIPTION + " TEXT," 
                + KEY_DEPT_CONTACT_PHONE + " TEXT," 
                + KEY_DEPT_ADMIN_NAME + " TEXT,"
                + KEY_DEPT_CREATED_AT + " INTEGER"
                + ")")
        
        // 创建应用状态表
        val createAppStatusTable = ("CREATE TABLE " + TABLE_APP_STATUS + "("
                + KEY_STATUS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_IS_ENABLED + " INTEGER,"
                + KEY_ENABLED_BY + " INTEGER,"
                + KEY_ENABLED_TIME + " INTEGER,"
                + KEY_DISABLED_BY + " INTEGER,"
                + KEY_DISABLED_TIME + " INTEGER"
                + ")")
        
        // 创建通知表
        val createNotificationsTable = ("CREATE TABLE " + TABLE_NOTIFICATIONS + "("
                + KEY_NOTIFICATION_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + KEY_NOTIFICATION_TITLE + " TEXT,"
                + KEY_NOTIFICATION_MESSAGE + " TEXT,"
                + KEY_NOTIFICATION_TYPE + " TEXT,"
                + KEY_NOTIFICATION_TARGET_USER_ID + " INTEGER,"
                + KEY_NOTIFICATION_TARGET_DEPARTMENT + " TEXT,"
                + KEY_NOTIFICATION_IS_READ + " INTEGER,"
                + KEY_NOTIFICATION_CREATE_TIME + " INTEGER,"
                + KEY_NOTIFICATION_CREATED_BY + " INTEGER"
                + ")")
        
        db.execSQL(createPatientsTable)
        db.execSQL(createUsersTable)
        db.execSQL(createDepartmentsTable)
        db.execSQL(createAppStatusTable)
        db.execSQL(createNotificationsTable)
        
        // 初始化默认数据
        initializeDefaultData(db)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // 强制重新创建所有表以确保结构正确
        Log.d("DATABASE_UPGRADE", "升级数据库从版本 $oldVersion 到 $newVersion")
        db.execSQL("DROP TABLE IF EXISTS $TABLE_PATIENTS")
        db.execSQL("DROP TABLE IF EXISTS $TABLE_USERS")
        db.execSQL("DROP TABLE IF EXISTS $TABLE_DEPARTMENTS")
        db.execSQL("DROP TABLE IF EXISTS $TABLE_APP_STATUS")
        db.execSQL("DROP TABLE IF EXISTS $TABLE_NOTIFICATIONS")
        onCreate(db)
    }

    private fun initializeDefaultData(db: SQLiteDatabase) {
        // 初始化默认用户
        initializeDefaultUsers(db)
        
        // 初始化默认科室
        initializeDefaultDepartments(db)
        
        // 初始化应用状态
        initializeAppStatus(db)
    }

    private fun initializeDefaultUsers(db: SQLiteDatabase) {
        // 大总管理员
        val superSuperAdmin = ContentValues()
        superSuperAdmin.put(KEY_USERNAME, "super_admin")
        superSuperAdmin.put(KEY_PASSWORD, "super123")
        superSuperAdmin.put(KEY_REAL_NAME, "大总管理员")
        superSuperAdmin.put(KEY_USER_DEPARTMENT, "系统管理")
        superSuperAdmin.put(KEY_USER_TYPE, UserType.SUPER_SUPER_ADMIN.name)
        superSuperAdmin.put(KEY_IS_ACTIVE, 1)
        superSuperAdmin.put(KEY_IS_APPROVED, 1)
        superSuperAdmin.put(KEY_APPROVED_TIME, System.currentTimeMillis())
        db.insert(TABLE_USERS, null, superSuperAdmin)
        
        // 总管理员
        val superAdmin = ContentValues()
        superAdmin.put(KEY_USERNAME, "admin")
        superAdmin.put(KEY_PASSWORD, "admin123")
        superAdmin.put(KEY_REAL_NAME, "总管理员")
        superAdmin.put(KEY_USER_DEPARTMENT, "系统管理")
        superAdmin.put(KEY_USER_TYPE, UserType.SUPER_ADMIN.name)
        superAdmin.put(KEY_IS_ACTIVE, 1)
        superAdmin.put(KEY_IS_APPROVED, 1)
        superAdmin.put(KEY_APPROVED_TIME, System.currentTimeMillis())
        db.insert(TABLE_USERS, null, superAdmin)
        
        // 科室管理员
        val deptAdmin = ContentValues()
        deptAdmin.put(KEY_USERNAME, "dept_admin")
        deptAdmin.put(KEY_PASSWORD, "dept123")
        deptAdmin.put(KEY_REAL_NAME, "科室管理员")
        deptAdmin.put(KEY_USER_DEPARTMENT, "内科")
        deptAdmin.put(KEY_USER_TYPE, UserType.DEPARTMENT_ADMIN.name)
        deptAdmin.put(KEY_IS_ACTIVE, 1)
        deptAdmin.put(KEY_IS_APPROVED, 1)
        deptAdmin.put(KEY_APPROVED_TIME, System.currentTimeMillis())
        val deptAdminId = db.insert(TABLE_USERS, null, deptAdmin)
        
        // 普通用户
        val user = ContentValues()
        user.put(KEY_USERNAME, "user")
        user.put(KEY_PASSWORD, "user123")
        user.put(KEY_REAL_NAME, "普通用户")
        user.put(KEY_USER_DEPARTMENT, "内科")
        user.put(KEY_USER_TYPE, UserType.USER.name)
        user.put(KEY_IS_ACTIVE, 1)
        user.put(KEY_IS_APPROVED, 1)
        user.put(KEY_APPROVED_TIME, System.currentTimeMillis())
        db.insert(TABLE_USERS, null, user)
    }

    private fun initializeDefaultDepartments(db: SQLiteDatabase) {
        // 内科
        val internalDept = ContentValues()
        internalDept.put(KEY_DEPT_NAME, "内科")
        internalDept.put(KEY_DEPT_ADMIN_ID, 3) // 科室管理员ID
        internalDept.put(KEY_DEPT_IS_ACTIVE, 1)
        internalDept.put(KEY_DEPT_DESCRIPTION, "内科科室")
        internalDept.put(KEY_DEPT_CONTACT_PHONE, "")
        internalDept.put(KEY_DEPT_ADMIN_NAME, "科室管理员")
        internalDept.put(KEY_DEPT_CREATED_AT, "")
        db.insert(TABLE_DEPARTMENTS, null, internalDept)
        
        // 外科
        val surgeryDept = ContentValues()
        surgeryDept.put(KEY_DEPT_NAME, "外科")
        surgeryDept.put(KEY_DEPT_ADMIN_ID, 0) // 暂无管理员
        surgeryDept.put(KEY_DEPT_IS_ACTIVE, 1)
        surgeryDept.put(KEY_DEPT_DESCRIPTION, "外科科室")
        surgeryDept.put(KEY_DEPT_CONTACT_PHONE, "")
        surgeryDept.put(KEY_DEPT_ADMIN_NAME, "")
        surgeryDept.put(KEY_DEPT_CREATED_AT, "")
        db.insert(TABLE_DEPARTMENTS, null, surgeryDept)
    }

    private fun initializeAppStatus(db: SQLiteDatabase) {
        val appStatus = ContentValues()
        appStatus.put(KEY_IS_ENABLED, 1)
        appStatus.put(KEY_ENABLED_BY, 1) // 大总管理员
        appStatus.put(KEY_ENABLED_TIME, System.currentTimeMillis())
        db.insert(TABLE_APP_STATUS, null, appStatus)
    }

    // 应用状态管理
    fun isAppEnabled(): Boolean {
        var cursor: Cursor? = null
        return try {
            val db = getReadableDB()
            cursor = db.query(TABLE_APP_STATUS, null, null, null, null, null, null)
            
            if (cursor.moveToFirst()) {
                cursor.getInt(cursor.getColumnIndexOrThrow(KEY_IS_ENABLED)) == 1
            } else {
                true
            }
        } catch (e: Exception) {
            Log.e("DatabaseHelper", "获取应用状态失败", e)
            true
        } finally {
            cursor?.close()
        }
    }

    fun setAppStatus(enabled: Boolean, userId: Long) {
        try {
            val db = getWritableDB()
            val values = ContentValues().apply {
                if (enabled) {
                    put(KEY_IS_ENABLED, 1)
                    put(KEY_ENABLED_BY, userId)
                    put(KEY_ENABLED_TIME, System.currentTimeMillis())
                } else {
                    put(KEY_IS_ENABLED, 0)
                    put(KEY_DISABLED_BY, userId)
                    put(KEY_DISABLED_TIME, System.currentTimeMillis())
                }
            }
            db.update(TABLE_APP_STATUS, values, null, null)
        } catch (e: Exception) {
            Log.e("DatabaseHelper", "设置应用状态失败", e)
        }
    }

    // 用户相关方法
    @WorkerThread
    fun authenticateUser(username: String, password: String): User? {
        // 检查应用是否启用
        if (!isAppEnabled()) {
            return null
        }
        
        var cursor: Cursor? = null
        return try {
            val db = getReadableDB()
            cursor = db.query(
                TABLE_USERS,
                null,
                "$KEY_USERNAME = ? AND $KEY_PASSWORD = ? AND $KEY_IS_ACTIVE = 1 AND $KEY_IS_APPROVED = 1",
                arrayOf(username, password),
                null, null, null
            )
            
            if (cursor.moveToFirst()) {
                buildUserFromCursor(cursor)
            } else {
                null
            }
        } catch (e: Exception) {
            Log.e("DatabaseHelper", "用户认证失败", e)
            null
        } finally {
            cursor?.close()
        }
    }

    /**
     * 从游标构建 User 对象
     */
    private fun buildUserFromCursor(cursor: Cursor): User {
        return User(
            id = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_USER_ID)),
            username = cursor.getString(cursor.getColumnIndexOrThrow(KEY_USERNAME)),
            password = cursor.getString(cursor.getColumnIndexOrThrow(KEY_PASSWORD)) ?: "",
            realName = cursor.getString(cursor.getColumnIndexOrThrow(KEY_REAL_NAME)) ?: "",
            department = cursor.getString(cursor.getColumnIndexOrThrow(KEY_USER_DEPARTMENT)) ?: "",
            userType = try {
                UserType.valueOf(cursor.getString(cursor.getColumnIndexOrThrow(KEY_USER_TYPE)))
            } catch (e: Exception) {
                UserType.USER
            },
            isActive = cursor.getInt(cursor.getColumnIndexOrThrow(KEY_IS_ACTIVE)) == 1,
            isApproved = cursor.getInt(cursor.getColumnIndexOrThrow(KEY_IS_APPROVED)) == 1,
            approvedBy = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_APPROVED_BY)),
            approvedTime = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_APPROVED_TIME)),
            registrationDate = "",
            phone = try {
                val phoneValue = cursor.getString(cursor.getColumnIndexOrThrow(KEY_PHONE))
                if (phoneValue == "null" || phoneValue == null) "" else phoneValue
            } catch (_: Exception) { "" }
        )
    }

    /**
     * 从游标构建 Patient 对象
     */
    private fun buildPatientFromCursor(cursor: Cursor): Patient {
        val dateFormat = SimpleDateFormat("yyyy-MM-dd", Locale.getDefault())
        val timeFormat = SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault())
        
        return Patient(
            id = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_ID)),
            name = cursor.getString(cursor.getColumnIndexOrThrow(KEY_NAME)) ?: "",
            gender = cursor.getString(cursor.getColumnIndexOrThrow("gender")) ?: "",
            examinationDate = try {
                dateFormat.parse(cursor.getString(cursor.getColumnIndexOrThrow(KEY_EXAMINATION_DATE))) ?: Date()
            } catch (e: Exception) {
                Date()
            },
            admissionDate = cursor.getString(cursor.getColumnIndexOrThrow("admissionDate")) ?: "",
            diagnosis = cursor.getString(cursor.getColumnIndexOrThrow("diagnosis")) ?: "",
            notes = cursor.getString(cursor.getColumnIndexOrThrow("notes")) ?: "",
            reporter = cursor.getString(cursor.getColumnIndexOrThrow(KEY_REPORTER)) ?: "",
            examinationName = cursor.getString(cursor.getColumnIndexOrThrow(KEY_EXAMINATION_NAME)) ?: "",
            department = cursor.getString(cursor.getColumnIndexOrThrow(KEY_DEPARTMENT)) ?: "",
            reportedBy = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_REPORTED_BY)),
            reportedByType = try {
                UserType.valueOf(cursor.getString(cursor.getColumnIndexOrThrow(KEY_REPORTED_BY_TYPE)))
            } catch (e: Exception) {
                UserType.USER
            },
            reportTime = try {
                timeFormat.parse(cursor.getString(cursor.getColumnIndexOrThrow(KEY_REPORT_TIME))) ?: Date()
            } catch (e: Exception) {
                Date()
            }
        )
    }

    fun findUserByUsername(username: String): User? {
        val db = this.readableDatabase
        val cursor = db.query(
            TABLE_USERS,
            null,
            "$KEY_USERNAME = ?",
            arrayOf(username),
            null, null, null
        )
        Log.d("LOGIN_DEBUG", "查找用户名: '$username'，结果数量: ${cursor.count}")
        return if (cursor.moveToFirst()) {
            try {
                Log.d("LOGIN_DEBUG", "开始解析用户数据...")
                
                val id = try { cursor.getLong(cursor.getColumnIndexOrThrow(KEY_USER_ID)) } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "获取ID失败: ${e.message}")
                    0L 
                }
                val usernameValue = try { cursor.getString(cursor.getColumnIndexOrThrow(KEY_USERNAME)) } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "获取用户名失败: ${e.message}")
                    "" 
                }
                val password = try { cursor.getString(cursor.getColumnIndexOrThrow(KEY_PASSWORD)) } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "获取密码失败: ${e.message}")
                    "" 
                }
                val realName = try { cursor.getString(cursor.getColumnIndexOrThrow(KEY_REAL_NAME)) } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "获取真实姓名失败: ${e.message}")
                    "" 
                }
                val department = try { cursor.getString(cursor.getColumnIndexOrThrow(KEY_USER_DEPARTMENT)) } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "获取科室失败: ${e.message}")
                    "" 
                }
                val userTypeStr = try { cursor.getString(cursor.getColumnIndexOrThrow(KEY_USER_TYPE)) } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "获取用户类型失败: ${e.message}")
                    null 
                }
                Log.d("LOGIN_DEBUG", "用户类型字符串: '$userTypeStr'")
                val userType = if (!userTypeStr.isNullOrEmpty()) try { 
                    UserType.valueOf(userTypeStr) 
                } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "解析用户类型失败: ${e.message}")
                    UserType.USER 
                } else UserType.USER
                val isActive = try { cursor.getInt(cursor.getColumnIndexOrThrow(KEY_IS_ACTIVE)) == 1 } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "获取激活状态失败: ${e.message}")
                    true 
                }
                val isApproved = try { cursor.getInt(cursor.getColumnIndexOrThrow(KEY_IS_APPROVED)) == 1 } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "获取审批状态失败: ${e.message}")
                    true 
                }
                val approvedBy = try { cursor.getLong(cursor.getColumnIndexOrThrow(KEY_APPROVED_BY)) } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "获取审批人失败: ${e.message}")
                    0L 
                }
                val approvedTime = try { cursor.getLong(cursor.getColumnIndexOrThrow(KEY_APPROVED_TIME)) } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "获取审批时间失败: ${e.message}")
                    0L 
                }
                val phone = try { 
                    val phoneValue = cursor.getString(cursor.getColumnIndexOrThrow(KEY_PHONE))
                    Log.d("LOGIN_DEBUG", "原始phone值: '$phoneValue'")
                    if (phoneValue == "null" || phoneValue == null) "" else phoneValue
                } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "获取电话失败: ${e.message}")
                    "" 
                }
               /* val email = try {
                    val emailValue = cursor.getString(cursor.getColumnIndexOrThrow(KEY_EMAIL))
                    Log.d("LOGIN_DEBUG", "原始email值: '$emailValue'")
                    if (emailValue == "null" || emailValue == null) "" else emailValue
                } catch (e: Exception) { 
                    Log.e("LOGIN_DEBUG", "获取邮箱失败: ${e.message}")
                    "" 
                }
*/
                Log.d("LOGIN_DEBUG", "解析完成，开始创建User对象...")
                Log.d("LOGIN_DEBUG", "字段值: id=$id, username='$usernameValue', password='$password', realName='$realName', department='$department', userType=$userType, isActive=$isActive, isApproved=$isApproved, approvedBy=$approvedBy, approvedTime=$approvedTime, phone='$phone'")
                
                // 逐个检查参数
                Log.d("LOGIN_DEBUG", "检查参数: id=${id::class.simpleName}, username=${usernameValue::class.simpleName}, password=${password::class.simpleName}")
                Log.d("LOGIN_DEBUG", "检查参数: realName=${realName::class.simpleName}, department=${department::class.simpleName}, userType=${userType::class.simpleName}")
                Log.d("LOGIN_DEBUG", "检查参数: isActive=${isActive::class.simpleName}, isApproved=${isApproved::class.simpleName}")
                Log.d("LOGIN_DEBUG", "检查参数: approvedBy=${approvedBy::class.simpleName}, approvedTime=${approvedTime::class.simpleName}")
                Log.d("LOGIN_DEBUG", "检查参数: phone=${phone::class.simpleName},")
                
                val user = User(
                    id = id,
                    username = usernameValue,
                    password = password,
                    realName = realName,
                    department = department,
                    userType = userType,
                    isActive = isActive,
                    isApproved = isApproved,
                    approvedBy = approvedBy,
                    approvedTime = approvedTime,
                    registrationDate = "", // 添加默认值
                    phone = phone,
                )
                Log.d("LOGIN_DEBUG", "User对象创建成功: ${user.username}")
                cursor.close()
                user
            } catch (e: Exception) {
                Log.e("LOGIN_DEBUG", "创建User对象时发生异常", e)
                e.printStackTrace()
                cursor.close()
                null
            }
        } else {
            Log.d("LOGIN_DEBUG", "未找到匹配的用户记录")
            cursor.close()
            null
        }
    }

    fun addUser(user: User): Long {
        val db = this.writableDatabase
        val values = ContentValues()
        
        values.put(KEY_USERNAME, user.username)
        values.put(KEY_PASSWORD, user.password)
        values.put(KEY_REAL_NAME, user.realName)
        values.put(KEY_USER_DEPARTMENT, user.department)
        values.put(KEY_USER_TYPE, user.userType.name)
        values.put(KEY_IS_ACTIVE, if (user.isActive) 1 else 0)
        values.put(KEY_IS_APPROVED, if (user.isApproved) 1 else 0)
        values.put(KEY_APPROVED_BY, user.approvedBy)
        values.put(KEY_APPROVED_TIME, user.approvedTime)
        values.put(KEY_PHONE, user.phone)
        // 删除：values.put(KEY_EMAIL, user.email)
        
        val id = db.insert(TABLE_USERS, null, values)
        db.close()
        return id
    }

    fun getAllUsers(): List<User> {
        val users = mutableListOf<User>()
        val selectQuery = "SELECT * FROM $TABLE_USERS ORDER BY $KEY_USER_TYPE, $KEY_REAL_NAME"
        
        val db = this.readableDatabase
        val cursor = db.rawQuery(selectQuery, null)
        
        try {
            if (cursor.moveToFirst()) {
                do {
                    val user = User(
                        id = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_USER_ID)),
                        username = cursor.getString(cursor.getColumnIndexOrThrow(KEY_USERNAME)),
                        password = cursor.getString(cursor.getColumnIndexOrThrow(KEY_PASSWORD)),
                        realName = cursor.getString(cursor.getColumnIndexOrThrow(KEY_REAL_NAME)),
                        department = cursor.getString(cursor.getColumnIndexOrThrow(KEY_USER_DEPARTMENT)),
                        userType = UserType.valueOf(cursor.getString(cursor.getColumnIndexOrThrow(KEY_USER_TYPE))),
                        isActive = cursor.getInt(cursor.getColumnIndexOrThrow(KEY_IS_ACTIVE)) == 1,
                        isApproved = cursor.getInt(cursor.getColumnIndexOrThrow(KEY_IS_APPROVED)) == 1,
                        approvedBy = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_APPROVED_BY)),
                        approvedTime = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_APPROVED_TIME)),
                        registrationDate = "", // 添加默认值
                        phone = try { 
                            val phoneValue = cursor.getString(cursor.getColumnIndexOrThrow(KEY_PHONE))
                            if (phoneValue == "null") "" else phoneValue
                        } catch (_: Exception) { "" }
                    )
                    users.add(user)
                } while (cursor.moveToNext())
            }
        } catch (e: Exception) {
            e.printStackTrace()
        } finally {
            cursor.close()
            db.close()
        }
        
        return users
    }

    fun getPendingUsers(): List<User> {
        val users = mutableListOf<User>()
        val selectQuery = "SELECT * FROM $TABLE_USERS WHERE $KEY_IS_APPROVED = 0 ORDER BY $KEY_USER_TYPE, $KEY_REAL_NAME"
        
        val db = this.readableDatabase
        val cursor = db.rawQuery(selectQuery, null)
        
        try {
            if (cursor.moveToFirst()) {
                do {
                    val user = User(
                        id = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_USER_ID)),
                        username = cursor.getString(cursor.getColumnIndexOrThrow(KEY_USERNAME)),
                        password = cursor.getString(cursor.getColumnIndexOrThrow(KEY_PASSWORD)),
                        realName = cursor.getString(cursor.getColumnIndexOrThrow(KEY_REAL_NAME)),
                        department = cursor.getString(cursor.getColumnIndexOrThrow(KEY_USER_DEPARTMENT)),
                        userType = UserType.valueOf(cursor.getString(cursor.getColumnIndexOrThrow(KEY_USER_TYPE))),
                        isActive = cursor.getInt(cursor.getColumnIndexOrThrow(KEY_IS_ACTIVE)) == 1,
                        isApproved = cursor.getInt(cursor.getColumnIndexOrThrow(KEY_IS_APPROVED)) == 1,
                        approvedBy = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_APPROVED_BY)),
                        approvedTime = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_APPROVED_TIME)),
                        registrationDate = "", // 添加默认值
                        phone = try { 
                            val phoneValue = cursor.getString(cursor.getColumnIndexOrThrow(KEY_PHONE))
                            if (phoneValue == "null") "" else phoneValue
                        } catch (_: Exception) { "" }
                    )
                    users.add(user)
                } while (cursor.moveToNext())
            }
        } catch (e: Exception) {
            e.printStackTrace()
        } finally {
            cursor.close()
            db.close()
        }
        
        return users
    }

    fun approveUser(userId: Long, approvedBy: Long): Boolean {
        val db = this.writableDatabase
        val values = ContentValues()
        values.put(KEY_IS_APPROVED, 1)
        values.put(KEY_APPROVED_BY, approvedBy)
        values.put(KEY_APPROVED_TIME, System.currentTimeMillis())
        
        val result = db.update(TABLE_USERS, values, "$KEY_USER_ID = ?", arrayOf(userId.toString()))
        db.close()
        return result > 0
    }

    fun deleteUser(userId: Long): Boolean {
        val db = this.writableDatabase
        val result = db.delete(TABLE_USERS, "$KEY_USER_ID = ?", arrayOf(userId.toString()))
        db.close()
        return result > 0
    }

    fun printAllUsers() {
        val db = this.readableDatabase
        val cursor = db.rawQuery("SELECT username, password FROM $TABLE_USERS", null)
        Log.d("ALL_USERS", "数据库中共有 ${cursor.count} 个用户")
        while (cursor.moveToNext()) {
            val username = cursor.getString(0)
            val password = cursor.getString(1)
            android.util.Log.d("ALL_USERS", "username: '$username', length: ${username.length}, password: $password")
            for (c in username) {
                android.util.Log.d("ALL_USERS", "char: '$c', ascii: ${c.code}")
            }
        }
        cursor.close()
        
        // 检查表结构
        try {
            val tableInfoCursor = db.rawQuery("PRAGMA table_info($TABLE_USERS)", null)
            Log.d("ALL_USERS", "用户表结构:")
            while (tableInfoCursor.moveToNext()) {
                val columnName = tableInfoCursor.getString(1)
                val columnType = tableInfoCursor.getString(2)
                Log.d("ALL_USERS", "列: $columnName, 类型: $columnType")
            }
            tableInfoCursor.close()
        } catch (e: Exception) {
            Log.e("ALL_USERS", "检查表结构失败", e)
        }
        
        db.close()
    }

    // 科室相关方法
    fun getAllDepartments(): List<Department> {
        val departments = mutableListOf<Department>()
        val selectQuery = "SELECT * FROM $TABLE_DEPARTMENTS ORDER BY $KEY_DEPT_NAME"
        
        val db = this.readableDatabase
        val cursor = db.rawQuery(selectQuery, null)
        
        try {
            if (cursor.moveToFirst()) {
                do {
                    val department = Department(
                        id = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_DEPT_ID)),
                        name = cursor.getString(cursor.getColumnIndexOrThrow(KEY_DEPT_NAME)),
                        adminId = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_DEPT_ADMIN_ID)),
                        isActive = cursor.getInt(cursor.getColumnIndexOrThrow(KEY_DEPT_IS_ACTIVE)) == 1,
                        createdAt = try { cursor.getString(cursor.getColumnIndexOrThrow(KEY_DEPT_CREATED_AT)) } catch (_: Exception) { "" },
                        description = try { cursor.getString(cursor.getColumnIndexOrThrow(KEY_DEPT_DESCRIPTION)) } catch (_: Exception) { "" },
                        contactPhone = try { cursor.getString(cursor.getColumnIndexOrThrow(KEY_DEPT_CONTACT_PHONE)) } catch (_: Exception) { "" },
                        adminName = try { cursor.getString(cursor.getColumnIndexOrThrow(KEY_DEPT_ADMIN_NAME)) } catch (_: Exception) { "" }
                    )
                    departments.add(department)
                } while (cursor.moveToNext())
            }
        } catch (e: Exception) {
            e.printStackTrace()
        } finally {
            cursor.close()
            db.close()
        }
        
        return departments
    }

    fun addDepartment(department: Department): Long {
        val db = this.writableDatabase
        val values = ContentValues()
        
        values.put(KEY_DEPT_NAME, department.name)
        values.put(KEY_DEPT_ADMIN_ID, department.adminId)
        values.put(KEY_DEPT_IS_ACTIVE, if (department.isActive) 1 else 0)
        values.put(KEY_DEPT_DESCRIPTION, department.description)
        values.put(KEY_DEPT_CONTACT_PHONE, department.contactPhone)
        values.put(KEY_DEPT_ADMIN_NAME, department.adminName)
        values.put(KEY_DEPT_CREATED_AT, department.createdAt)
        
        val id = db.insert(TABLE_DEPARTMENTS, null, values)
        db.close()
        return id
    }

    fun updateDepartment(department: Department): Int {
        val db = this.writableDatabase
        val values = ContentValues()
        values.put(KEY_DEPT_NAME, department.name)
        values.put(KEY_DEPT_ADMIN_ID, department.adminId)
        values.put(KEY_DEPT_IS_ACTIVE, if (department.isActive) 1 else 0)
        values.put("description", department.description)
        values.put("contactPhone", department.contactPhone)
        values.put("adminName", department.adminName)
        val result = db.update(TABLE_DEPARTMENTS, values, "$KEY_DEPT_ID = ?", arrayOf(department.id.toString()))
        db.close()
        return result
    }
    fun removeUserFromDepartment(userId: Long): Int {
        val db = this.writableDatabase
        val values = ContentValues()
        values.put(KEY_USER_DEPARTMENT, "")
        val result = db.update(TABLE_USERS, values, "$KEY_USER_ID = ?", arrayOf(userId.toString()))
        db.close()
        return result
    }

    // 患者相关方法（带权限控制）
    fun addPatient(patient: Patient): Long {
        val db = this.writableDatabase
        val values = ContentValues()
        val dateFormat = SimpleDateFormat("yyyy-MM-dd", Locale.getDefault())
        val timeFormat = SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault())
        
        values.put(KEY_NAME, patient.name)
        values.put("gender", patient.gender)
        values.put(KEY_EXAMINATION_DATE, dateFormat.format(patient.examinationDate))
        values.put("admissionDate", patient.admissionDate)
        values.put("diagnosis", patient.diagnosis)
        values.put("notes", patient.notes)
        values.put(KEY_REPORTER, patient.reporter)
        values.put(KEY_EXAMINATION_NAME, patient.examinationName)
        values.put(KEY_DEPARTMENT, patient.department)
        values.put(KEY_REPORTED_BY, patient.reportedBy)
        values.put(KEY_REPORTED_BY_TYPE, patient.reportedByType.name)
        values.put(KEY_REPORT_TIME, timeFormat.format(patient.reportTime))
        
        val id = db.insert(TABLE_PATIENTS, null, values)
        db.close()
        return id
    }

    fun updatePatient(patient: Patient): Int {
        val db = this.writableDatabase
        val values = ContentValues()
        values.put(KEY_NAME, patient.name)
        values.put("gender", patient.gender)
        values.put(KEY_EXAMINATION_DATE, java.text.SimpleDateFormat("yyyy-MM-dd", java.util.Locale.getDefault()).format(patient.examinationDate))
        values.put("admissionDate", patient.admissionDate)
        values.put("diagnosis", patient.diagnosis)
        values.put("notes", patient.notes)
        values.put(KEY_REPORTER, patient.reporter)
        values.put(KEY_EXAMINATION_NAME, patient.examinationName)
        values.put(KEY_DEPARTMENT, patient.department)
        values.put(KEY_REPORTED_BY, patient.reportedBy)
        values.put(KEY_REPORTED_BY_TYPE, patient.reportedByType.name)
        values.put(KEY_REPORT_TIME, java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss", java.util.Locale.getDefault()).format(patient.reportTime))
        val result = db.update(TABLE_PATIENTS, values, "$KEY_ID = ?", arrayOf(patient.id.toString()))
        db.close()
        return result
    }

    fun getPatientsByUserType(currentUser: User): List<Patient> {
        val patients = mutableListOf<Patient>()
        val selectQuery = when (currentUser.userType) {
            UserType.SUPER_SUPER_ADMIN, UserType.SUPER_ADMIN -> 
                "SELECT * FROM $TABLE_PATIENTS ORDER BY $KEY_REPORT_TIME DESC"
            UserType.DEPARTMENT_ADMIN -> 
                "SELECT * FROM $TABLE_PATIENTS WHERE $KEY_DEPARTMENT = '${currentUser.department}' ORDER BY $KEY_REPORT_TIME DESC"
            UserType.USER -> 
                "SELECT * FROM $TABLE_PATIENTS WHERE $KEY_REPORTED_BY = ${currentUser.id} ORDER BY $KEY_REPORT_TIME DESC"
        }
        
        val db = this.readableDatabase
        val cursor = db.rawQuery(selectQuery, null)
        
        try {
        if (cursor.moveToFirst()) {
            do {
                    val dateFormat = SimpleDateFormat("yyyy-MM-dd", Locale.getDefault())
                    val timeFormat = SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault())
                    
                val patient = Patient(
                    id = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_ID)),
                    name = cursor.getString(cursor.getColumnIndexOrThrow(KEY_NAME)),
                    gender = try { cursor.getString(cursor.getColumnIndexOrThrow("gender")) } catch (_: Exception) { "" },
                    examinationDate = try {
                        dateFormat.parse(cursor.getString(cursor.getColumnIndexOrThrow(KEY_EXAMINATION_DATE))) ?: Date()
                    } catch (e: Exception) {
                        Date()
                    },
                    admissionDate = try { cursor.getString(cursor.getColumnIndexOrThrow("admissionDate")) } catch (_: Exception) { "" },
                    diagnosis = try { cursor.getString(cursor.getColumnIndexOrThrow("diagnosis")) } catch (_: Exception) { "" },
                    notes = try { cursor.getString(cursor.getColumnIndexOrThrow("notes")) } catch (_: Exception) { "" },
                    reporter = cursor.getString(cursor.getColumnIndexOrThrow(KEY_REPORTER)),
                    examinationName = cursor.getString(cursor.getColumnIndexOrThrow(KEY_EXAMINATION_NAME)),
                    department = cursor.getString(cursor.getColumnIndexOrThrow(KEY_DEPARTMENT)),
                    reportedBy = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_REPORTED_BY)),
                    reportedByType = try {
                        UserType.valueOf(cursor.getString(cursor.getColumnIndexOrThrow(KEY_REPORTED_BY_TYPE)))
                    } catch (e: Exception) {
                        UserType.USER
                    },
                    reportTime = try {
                        timeFormat.parse(cursor.getString(cursor.getColumnIndexOrThrow(KEY_REPORT_TIME))) ?: Date()
                    } catch (e: Exception) {
                        Date()
                    }
                )
                patients.add(patient)
            } while (cursor.moveToNext())
            }
        } catch (e: Exception) {
            e.printStackTrace()
        } finally {
            cursor.close()
            db.close()
        }
        
        return patients
    }

    fun deletePatient(patientId: Long): Int {
        val db = this.writableDatabase
        val result = db.delete(TABLE_PATIENTS, "$KEY_ID = ?", arrayOf(patientId.toString()))
        db.close()
        return result
    }

    // 通知相关方法
    fun addNotification(notification: Notification): Long {
        val db = this.writableDatabase
        val values = ContentValues()
        
        values.put(KEY_NOTIFICATION_TITLE, notification.title)
        values.put(KEY_NOTIFICATION_MESSAGE, notification.message)
        values.put(KEY_NOTIFICATION_TYPE, notification.type.name)
        values.put(KEY_NOTIFICATION_TARGET_USER_ID, notification.targetUserId)
        values.put(KEY_NOTIFICATION_TARGET_DEPARTMENT, notification.targetDepartment)
        values.put(KEY_NOTIFICATION_IS_READ, if (notification.isRead) 1 else 0)
        values.put(KEY_NOTIFICATION_CREATE_TIME, notification.createTime.time)
        values.put(KEY_NOTIFICATION_CREATED_BY, notification.createdBy)
        
        val id = db.insert(TABLE_NOTIFICATIONS, null, values)
        db.close()
        return id
    }

    fun getNotificationsForUser(user: User): List<Notification> {
        val notifications = mutableListOf<Notification>()
        val selectQuery = """
            SELECT * FROM $TABLE_NOTIFICATIONS 
            WHERE ($KEY_NOTIFICATION_TARGET_USER_ID = 0 OR $KEY_NOTIFICATION_TARGET_USER_ID = ${user.id})
            AND ($KEY_NOTIFICATION_TARGET_DEPARTMENT = '' OR $KEY_NOTIFICATION_TARGET_DEPARTMENT = '${user.department}')
            ORDER BY $KEY_NOTIFICATION_CREATE_TIME DESC
        """.trimIndent()
        
        val db = this.readableDatabase
        val cursor = db.rawQuery(selectQuery, null)
        
        try {
            if (cursor.moveToFirst()) {
                do {
                    val notification = Notification(
                        id = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_NOTIFICATION_ID)),
                        title = cursor.getString(cursor.getColumnIndexOrThrow(KEY_NOTIFICATION_TITLE)),
                        message = cursor.getString(cursor.getColumnIndexOrThrow(KEY_NOTIFICATION_MESSAGE)),
                        type = NotificationType.valueOf(cursor.getString(cursor.getColumnIndexOrThrow(KEY_NOTIFICATION_TYPE))),
                        targetUserId = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_NOTIFICATION_TARGET_USER_ID)),
                        targetDepartment = cursor.getString(cursor.getColumnIndexOrThrow(KEY_NOTIFICATION_TARGET_DEPARTMENT)),
                        isRead = cursor.getInt(cursor.getColumnIndexOrThrow(KEY_NOTIFICATION_IS_READ)) == 1,
                        createTime = Date(cursor.getLong(cursor.getColumnIndexOrThrow(KEY_NOTIFICATION_CREATE_TIME))),
                        createdBy = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_NOTIFICATION_CREATED_BY))
                    )
                    notifications.add(notification)
                } while (cursor.moveToNext())
            }
        } catch (e: Exception) {
            e.printStackTrace()
        } finally {
            cursor.close()
            db.close()
        }
        
        return notifications
    }

    fun markNotificationAsRead(notificationId: Long): Boolean {
        val db = this.writableDatabase
        val values = ContentValues()
        values.put(KEY_NOTIFICATION_IS_READ, 1)
        
        val result = db.update(TABLE_NOTIFICATIONS, values, "$KEY_NOTIFICATION_ID = ?", arrayOf(notificationId.toString()))
        db.close()
        return result > 0
    }

    fun getUnreadNotificationCount(user: User): Int {
        val notifications = getNotificationsForUser(user)
        return notifications.count { !it.isRead }
    }

    fun deleteAllNotificationsForUser(user: User): Int {
        val db = this.writableDatabase
        val result = db.delete(TABLE_NOTIFICATIONS, 
            "($KEY_NOTIFICATION_TARGET_USER_ID = 0 OR $KEY_NOTIFICATION_TARGET_USER_ID = ?) " +
            "AND ($KEY_NOTIFICATION_TARGET_DEPARTMENT = '' OR $KEY_NOTIFICATION_TARGET_DEPARTMENT = ?)",
            arrayOf(user.id.toString(), user.department))
        db.close()
        return result
    }

    // 数据导出相关方法
    fun getPatientsByDateRange(startDate: String, endDate: String): List<Patient> {
        val patients = mutableListOf<Patient>()
        val selectQuery = """
            SELECT * FROM $TABLE_PATIENTS 
            WHERE $KEY_EXAMINATION_DATE BETWEEN ? AND ? 
            ORDER BY $KEY_REPORT_TIME DESC
        """.trimIndent()
        
        val db = this.readableDatabase
        val cursor = db.rawQuery(selectQuery, arrayOf(startDate, endDate))
        
        try {
            if (cursor.moveToFirst()) {
                do {
                    val dateFormat = SimpleDateFormat("yyyy-MM-dd", Locale.getDefault())
                    val timeFormat = SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault())
                    
                    val patient = Patient(
                        id = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_ID)),
                        name = cursor.getString(cursor.getColumnIndexOrThrow(KEY_NAME)),
                        /*age = try { cursor.getInt(cursor.getColumnIndexOrThrow("age")) } catch (_: Exception) { 0 },*/
                        gender = try { cursor.getString(cursor.getColumnIndexOrThrow("gender")) } catch (_: Exception) { "" },
                        examinationDate = try {
                            dateFormat.parse(cursor.getString(cursor.getColumnIndexOrThrow(KEY_EXAMINATION_DATE))) ?: Date()
                        } catch (e: Exception) {
                            Date()
                        },
                        admissionDate = try { cursor.getString(cursor.getColumnIndexOrThrow("admissionDate")) } catch (_: Exception) { "" },
                        diagnosis = try { cursor.getString(cursor.getColumnIndexOrThrow("diagnosis")) } catch (_: Exception) { "" },
                        /*doctor = try { cursor.getString(cursor.getColumnIndexOrThrow("doctor")) } catch (_: Exception) { "" },
                        phone = try { cursor.getString(cursor.getColumnIndexOrThrow("phone")) } catch (_: Exception) { "" },*/
                        notes = try { cursor.getString(cursor.getColumnIndexOrThrow("notes")) } catch (_: Exception) { "" },
                        reporter = cursor.getString(cursor.getColumnIndexOrThrow(KEY_REPORTER)),
                        examinationName = cursor.getString(cursor.getColumnIndexOrThrow(KEY_EXAMINATION_NAME)),
                        department = cursor.getString(cursor.getColumnIndexOrThrow(KEY_DEPARTMENT)),
                        reportedBy = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_REPORTED_BY)),
                        reportedByType = try {
                            UserType.valueOf(cursor.getString(cursor.getColumnIndexOrThrow(KEY_REPORTED_BY_TYPE)))
                        } catch (e: Exception) {
                            UserType.USER
                        },
                        reportTime = try {
                            timeFormat.parse(cursor.getString(cursor.getColumnIndexOrThrow(KEY_REPORT_TIME))) ?: Date()
                        } catch (e: Exception) {
                            Date()
                        }
                    )
                    patients.add(patient)
                } while (cursor.moveToNext())
            }
        } catch (e: Exception) {
            e.printStackTrace()
        } finally {
            cursor.close()
            db.close()
        }
        
        return patients
    }

    fun getPatientsFiltered(
        currentUser: User,
        department: String?,
        reporter: String?,
        startDate: String?,
        endDate: String?,
        keyword: String?
    ): List<Patient> {
        val patients = mutableListOf<Patient>()
        val whereClauses = mutableListOf<String>()
        val args = mutableListOf<String>()
        // 权限过滤
        when (currentUser.userType) {
            UserType.SUPER_SUPER_ADMIN, UserType.SUPER_ADMIN -> {}
            UserType.DEPARTMENT_ADMIN -> {
                whereClauses.add("$KEY_DEPARTMENT = ?")
                args.add(currentUser.department)
            }
            UserType.USER -> {
                whereClauses.add("$KEY_REPORTED_BY = ?")
                args.add(currentUser.id.toString())
            }
        }
        if (!department.isNullOrEmpty() && department != "全部") {
            whereClauses.add("$KEY_DEPARTMENT = ?")
            args.add(department)
        }
        if (!reporter.isNullOrEmpty()) {
            whereClauses.add("$KEY_REPORTER LIKE ?")
            args.add("%$reporter%")
        }
        if (!startDate.isNullOrEmpty() && !endDate.isNullOrEmpty()) {
            whereClauses.add("$KEY_EXAMINATION_DATE BETWEEN ? AND ?")
            args.add(startDate)
            args.add(endDate)
        }
        if (!keyword.isNullOrEmpty()) {
            whereClauses.add("($KEY_NAME LIKE ? OR $KEY_EXAMINATION_NAME LIKE ? OR $KEY_REPORTER LIKE ? OR diagnosis LIKE ? OR notes LIKE ?)")
            repeat(5) { args.add("%$keyword%") }
        }
        val where = if (whereClauses.isNotEmpty()) "WHERE ${whereClauses.joinToString(" AND ")}" else ""
        val selectQuery = "SELECT * FROM $TABLE_PATIENTS $where ORDER BY $KEY_REPORT_TIME DESC"
        val db = this.readableDatabase
        val cursor = db.rawQuery(selectQuery, args.toTypedArray())
        try {
            if (cursor.moveToFirst()) {
                do {
                    val dateFormat = java.text.SimpleDateFormat("yyyy-MM-dd", java.util.Locale.getDefault())
                    val timeFormat = java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss", java.util.Locale.getDefault())
                    val patient = Patient(
                        id = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_ID)),
                        name = cursor.getString(cursor.getColumnIndexOrThrow(KEY_NAME)),
                       /* age = try { cursor.getInt(cursor.getColumnIndexOrThrow("age")) } catch (_: Exception) { 0 },*/
                        gender = try { cursor.getString(cursor.getColumnIndexOrThrow("gender")) } catch (_: Exception) { "" },
                        examinationDate = try { dateFormat.parse(cursor.getString(cursor.getColumnIndexOrThrow(KEY_EXAMINATION_DATE))) ?: java.util.Date() } catch (e: Exception) { java.util.Date() },
                        admissionDate = try { cursor.getString(cursor.getColumnIndexOrThrow("admissionDate")) } catch (_: Exception) { "" },
                        diagnosis = try { cursor.getString(cursor.getColumnIndexOrThrow("diagnosis")) } catch (_: Exception) { "" },
                        /*doctor = try { cursor.getString(cursor.getColumnIndexOrThrow("doctor")) } catch (_: Exception) { "" },
                        phone = try { cursor.getString(cursor.getColumnIndexOrThrow("phone")) } catch (_: Exception) { "" },*/
                        notes = try { cursor.getString(cursor.getColumnIndexOrThrow("notes")) } catch (_: Exception) { "" },
                        reporter = cursor.getString(cursor.getColumnIndexOrThrow(KEY_REPORTER)),
                        examinationName = cursor.getString(cursor.getColumnIndexOrThrow(KEY_EXAMINATION_NAME)),
                        department = cursor.getString(cursor.getColumnIndexOrThrow(KEY_DEPARTMENT)),
                        reportedBy = cursor.getLong(cursor.getColumnIndexOrThrow(KEY_REPORTED_BY)),
                        reportedByType = try { UserType.valueOf(cursor.getString(cursor.getColumnIndexOrThrow(KEY_REPORTED_BY_TYPE))) } catch (_: Exception) { UserType.USER },
                        reportTime = try { timeFormat.parse(cursor.getString(cursor.getColumnIndexOrThrow(KEY_REPORT_TIME))) ?: java.util.Date() } catch (_: Exception) { java.util.Date() }
                    )
                    patients.add(patient)
                } while (cursor.moveToNext())
            }
        } catch (e: Exception) {
            e.printStackTrace()
        } finally {
            cursor.close()
            db.close()
        }
        return patients
    }

    /**
     * 检查数据库是否可用
     */
    fun isDatabaseAvailable(): Boolean {
        return try {
            val db = this.readableDatabase
            // 尝试执行一个简单的查询来测试数据库连接
            val cursor = db.rawQuery("SELECT 1", null)
            cursor.close()
            db.close()
            true
        } catch (e: Exception) {
            Log.e("DatabaseHelper", "数据库不可用", e)
            false
        }
    }
    
    /**
     * 安全地添加患者
     */
    fun addPatientSafely(patient: Patient): Long {
        return try {
            addPatient(patient)
        } catch (e: Exception) {
            Log.e("DatabaseHelper", "添加患者失败", e)
            -1
        }
    }
    
    /**
     * 安全地更新患者
     */
    fun updatePatientSafely(patient: Patient): Int {
        return try {
            updatePatient(patient)
        } catch (e: Exception) {
            Log.e("DatabaseHelper", "更新患者失败", e)
            -1
        }
    }
    
    /**
     * 获取所有患者数据（用于数据同步）
     */
    fun getAllPatients(): List<Patient> {
        val patients = mutableListOf<Patient>()
        val selectQuery = "SELECT * FROM $TABLE_PATIENTS ORDER BY $KEY_REPORT_TIME DESC"
        
        val db = this.readableDatabase
        val cursor = db.rawQuery(selectQuery, null)
        
        try {
            if (cursor.moveToFirst()) {
                do {
                    val patient = buildPatientFromCursor(cursor)
                    patients.add(patient)
                } while (cursor.moveToNext())
            }
        } catch (e: Exception) {
            Log.e("DatabaseHelper", "获取所有患者失败", e)
        } finally {
            cursor.close()
            db.close()
        }
        
        return patients
    }
    
    /**
     * 根据ID获取患者（用于数据同步）
     */
    fun getPatientById(patientId: Long): Patient? {
        val db = this.readableDatabase
        val cursor = db.query(
            TABLE_PATIENTS,
            null,
            "$KEY_ID = ?",
            arrayOf(patientId.toString()),
            null, null, null
        )
        
        return try {
            if (cursor.moveToFirst()) {
                buildPatientFromCursor(cursor)
            } else {
                null
            }
        } catch (e: Exception) {
            Log.e("DatabaseHelper", "根据ID获取患者失败", e)
            null
        } finally {
            cursor.close()
            db.close()
        }
    }

} 